﻿using BanHangWPF.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BanHangWPF.Database
{
    public class MySQLHelper
    {
        private const String SERVER="localhost";
        private const String DATABASE = "he_thong_ban_hang";
        private const String UID = "root";
        private const String PASSWORD = "admin";
        string MyConString = "SERVER=" + SERVER + ";" +"DATABASE=" + DATABASE + ";" +"UID=" + UID + ";" +"PASSWORD=" + PASSWORD + ";";

        MySqlConnection connection;
        public MySQLHelper()
        {            
            //connection = new MySqlConnection(MyConString);
            //MySqlCommand command = connection.CreateCommand();
            //MySqlDataReader Reader;
            //command.CommandText = "select count(*) from ten_hang";
            //connection.Open();
            //Reader = command.ExecuteReader();
            //while (Reader.Read())
            //{
            //    for (int i = 0; i < Reader.FieldCount; i++)
            //        Debug.WriteLine(Reader.GetValue(i));
            //}
            //connection.Close();
        }

        public Boolean addHang(Hang hang)
        {
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "Insert into ten_hang (Mahang, Tenhang, Donvihop, Donvivien, Soluongvien, Nhomhang, Motathem, Timecreate,Timesend) values ('" + hang.MaHang + "', '" + hang.TenHang + "', '"
                + hang.DonViHop + "', '" + hang.DonViVien + "', '" + hang.SoLuongVien + "', '" + hang.NhomHang + "', '" + hang.MoTaThem + "', '" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "', null)";
            connection.Open();
            Debug.WriteLine(command.CommandText);
            int numrow = command.ExecuteNonQuery();
            //int numrow = 0;
            connection.Close();
            if (numrow > 0) 
                return true;
            else 
                return false;
        }

        public ObservableCollection<Hang> getAllHang()
        {
            connection = new MySqlConnection(MyConString);
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;
            command.CommandText = "select * from ten_hang";
            connection.Open();
            Reader = command.ExecuteReader();
            ObservableCollection<Hang>  allHang = new ObservableCollection<Hang>();
            while (Reader.Read())
            {
                allHang.Add(new Hang(
                    Reader["Mahang"].ToString(),
                    Reader["TenHang"].ToString(),
                    Reader["Donvihop"].ToString(),
                    Reader["Donvivien"].ToString(), 
                    Convert.ToInt32(Reader["Soluongvien"]),
                    Reader["Nhomhang"].ToString(),
                    Reader["Motathem"].ToString(),
                    Reader["Timecreate"].ToString(),
                    Reader["Timesend"].ToString()));
            }
            connection.Close();
            return allHang;
        }
    }
}
